package orest.front.membercenter;

import java.sql.Connection;
import java.sql.SQLException;

import net.ssta.common.Constants;
import net.ssta.core.db.DBConnectionProvider;
import net.ssta.core.db.DataBaseExecutor;
import net.ssta.core.db.PagingInfo;
import net.ssta.core.db.Record;
import net.ssta.core.db.RecordSet;

public class AchieveTrackSql {
	
	/**
	 * 根据机构id获取机构成果信息
	 * @param unitid
	 * @return
	 */
	public static String getArchieveByUserid(String userid){
		StringBuffer sql=new StringBuffer();
		
		sql.append("SELECT K.KJCGID,K.UNITID,K.TOPICWORDS,K.KJCG_ITEM_3,K.KJCG_ITEM_8 FROM T_KJCG K,T_USERUPGRADE U ");
		sql.append("WHERE K.UNITID=U.FKID AND U.FKTYPE='0' AND U.USERID='").append(userid).append("'"); 
		
    	return sql.toString();
	}
	
	/**
	 * 根据机构id获取机构对应的成果跟踪数量
	 * @param unitid
	 * @return
	 */
	public static String countArchieveByUserid(String userid){
		StringBuffer sql=new StringBuffer();
		
		sql.append("SELECT K.KJCGID KJCGID,COUNT(K.KJCGID) KJCGCOUNT FROM T_ACHIEVETRACK A,T_KJCG K ");
		sql.append("WHERE A.KJCGID=K.KJCGID AND A.USERID='").append(userid).append("' ");
		sql.append("GROUP BY K.KJCGID ");
		
    	return sql.toString();
	}
	
	/**
	 * 根据成果id获取成果详细信息
	 * @param unitid
	 * @return
	 */
	public static String getArchieveInfoById(String achieveid){
		StringBuffer sql=new StringBuffer();
		
		sql.append(" SELECT * ");
		sql.append(" FROM T_KJCG A WHERE 1=1 ");
		sql.append(" AND A.KJCGID='").append(achieveid).append("'");
		
    	return sql.toString();
	}
	
	/**
	 * 根据成果id获取对应的成果信息跟踪列表
	 * @param unitid
	 * @return
	 */
	public static String getFollowInfoByArchieveId(String achieveid){
		StringBuffer sql=new StringBuffer();
		
		sql.append(" SELECT * ");
		sql.append(" FROM T_KJCGGZ A WHERE 1=1 ");
		sql.append(" AND A.KJCGID='").append(achieveid).append("'");
		
    	return sql.toString();
	}
	
	
	public static String getArchieveTrackByUserid(String userid,String kjcgid){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T.ID,T.KJCGID,T.TRACKINFO,T.SUBMITDATE,T.USERID,T.IFCHECK,K.KJCG_ITEM_3 FROM T_ACHIEVETRACK T,T_KJCG K WHERE 1=1 AND T.KJCGID=K.KJCGID ");
		sql.append(" AND T.USERID='").append(userid).append("' ");
		sql.append(" AND T.KJCGID='").append(kjcgid).append("' ");
		sql.append(" ORDER BY T.SUBMITDATE DESC ");
		
		return sql.toString();
	}
	
	public static String deleteTrackById(String trackid){
		StringBuffer sql=new StringBuffer();
		sql.append(" DELETE FROM T_ACHIEVETRACK WHERE ID ='").append(trackid).append("'");

		return sql.toString();
	}
}
